package cn.easier.activity.console.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import cn.easier.activity.web.entity.ActivityRecord;

import java.util.Date;
import java.util.List;

/**
 * 活动记录统计 Repository
 */
@Repository
public interface ActivityRecordStatisticsRepository extends JpaRepository<ActivityRecord, String>,JpaSpecificationExecutor<ActivityRecord>  {

    /**
     * 获取用户券码总记录
     * （活动有可能没有，奖品有可能会删除，券码有录入券码和H5券码。
     * 奖品历史记录需要真实记录，所以不能使用inner join）
     *
     * @param phoneNum 用户
     * @return
     */
    @Query(value = "  \n" +
            "  SELECT\n" +
            "    COUNT(1)\n" +
            "  FROM\n" +
            "    `activity_record` ar\n" +
            "    LEFT JOIN `activity_info` ai\n" +
            "      ON ar.`activity_id` = ai.`id`\n" +
            "    LEFT JOIN `activity_prize` ap\n" +
            "      ON ar.`prize_id` = ap.`id`\n" +
            "    LEFT JOIN `activity_coupon_code` acc\n" +
            "      ON ar.`coupon_code_id` = acc.`id`\n" +
            "  WHERE ap.`prize_type` = 2\n" +
            "    AND ar.phone_num = ?", nativeQuery = true)
    int countActivityCouponCodeRecordByPhoneNum(String phoneNum);

    /**
     * 分页获取用户券码列表
     * （活动有可能没有，奖品有可能会删除，券码有录入券码和H5券码。
     * 奖品历史记录需要真实记录，所以不能使用inner join）
     *
     * @param phoneNum  用户
     * @param pageIndex 起始位置
     * @param pageNum   条目
     * @return
     */
    @Query(value = "SELECT\n" +
            "  ar.`phone_num` AS phoneNum,\n" +
            "  ar.`create_date` AS createDate,\n" +
            "  ai.`name` AS activityName,\n" +
            "  ap.`prize_name` AS prizeName,\n" +
            "  ap.`prize_type` AS prizeTypeIndex,\n" +
            "  ar.`is_awarded` AS isAwarded,\n" +
            "  ar.`exchange_state` AS exchangeStateIndex,\n" +
            "  ar.`bill` AS bill,\n" +
            "  ap.`url` AS url,\n" +
            "  acc.`coupon_type` AS couponTypeIndex,\n" +
            "  acc.`code` AS code,\n" +
            "  acc.`secret_key` AS secretKey\n" +
            "FROM\n" +
            "  `activity_record` ar\n" +
            "  LEFT JOIN `activity_info` ai\n" +
            "    ON ar.`activity_id` = ai.`id`\n" +
            "  LEFT JOIN `activity_prize` ap\n" +
            "    ON ar.`prize_id` = ap.`id`\n" +
            "  LEFT JOIN `activity_coupon_code` acc\n" +
            "    ON ar.`coupon_code_id` = acc.`id`\n" +
            "WHERE ap.`prize_type` = 2\n" +
            "  AND ar.phone_num = ?\n" +
            "  ORDER BY ar.`create_date` DESC\n" +
            "  LIMIT ?,?", nativeQuery = true)
    Object[] findActivityCouponCodeRecordsByPhoneNum(String phoneNum, int pageIndex, int pageNum);

    @Query(value = "select a.* from activity_record a where ((:phoneNum is null or :phoneNum = '') or a.`phone_num` = :phoneNum) and  " +
            " ((:activityId is null or :activityId = '') or a.`activity_id` = :activityId) and ((:recordNo is null or :recordNo = '') or a.`record_no` = :recordNo) and ((:prizeType is null or :prizeType = 0) or a.`prize_type` = :prizeType)  and a.activity_id in :idList order by a.create_date desc LIMIT :pageCount , :pageSize " ,nativeQuery = true)
    List<ActivityRecord> findBy(@Param("activityId") Integer activityId,@Param("phoneNum") String phoneNum,@Param("recordNo") String recordNo,@Param("prizeType") Integer prizeType ,@Param("idList") List<Integer> idList ,@Param("pageCount") int pageCount, @Param("pageSize") int pageSize);

    @Query(value = "select count(a.record_no) from activity_record a where ((:phoneNum is null or :phoneNum = '') or a.`phone_num` = :phoneNum) and  " +
            " ((:activityId is null or :activityId = '') or a.`activity_id` = :activityId) and ((:recordNo is null or :recordNo = '') or a.`record_no` = :recordNo) and ((:prizeType is null or :prizeType = 0) or a.`prize_type` = :prizeType) and a.activity_id in :idList order by a.create_date desc " ,nativeQuery = true)
    long findByCount(@Param("activityId") Integer activityId,@Param("phoneNum") String phoneNum,@Param("recordNo") String recordNo,@Param("prizeType") Integer prizeType,@Param("idList") List<Integer> idList);

    @Query(value = "select a.* from activity_record a where ((:phoneNum is null or :phoneNum = '') or a.`phone_num` = :phoneNum) and  " +
            " ((:activityId is null or :activityId = '') or a.`activity_id` = :activityId) and ((:recordNo is null or :recordNo = '') or a.`record_no` = :recordNo) and ((:prizeType is null or :prizeType = 0) or a.`prize_type` = :prizeType) and a.activity_id in :idList order by a.create_date desc " ,nativeQuery = true)
    List<ActivityRecord> findByCountForExport(@Param("activityId") Integer activityId,@Param("phoneNum") String phoneNum,@Param("recordNo") String recordNo,@Param("prizeType") Integer prizeType,@Param("idList") List<Integer> idList);

    @Query(value = "SELECT a.activity_id,a.activity_name,a.phone_num,a.create_date,a.prize_name,b.project_name FROM activity_record a LEFT JOIN signing_message b ON a.phone_num = b.user_phone_num WHERE a.activity_id = 306 AND a.prize_type = 2 ORDER BY a.create_date DESC " ,nativeQuery = true)
    List<Object[]> findByCountForSignMessage();

    List<ActivityRecord> findByActivityIdAndCreateDateBetween(Integer activityId, Date createDate, Date createDate2);

    @Query(value = "select a.* FROM activity_record a WHERE a.activity_id in (select a.id FROM activity_info a WHERE a.`status` !=2) and ((:prizeType is null or :prizeType = 0) or a.`prize_type` = :prizeType) ORDER BY a.create_date DESC " ,nativeQuery = true)
    List<ActivityRecord> findBy1(@Param("prizeType") Integer prizeType);

    @Query(value = "select a.* from activity_record a where a.record_no in :recordIdList",nativeQuery = true)
    List<ActivityRecord> findByRecordNo(@Param("recordIdList") List<String> recordIdList);
}
